package com.ztc.ZtcRoute.dao;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;
import com.aqgj.util.BaseDaoImpl;
import com.ztc.ZtcRoute.bean.ZtcRoute;

/**
 * <p>
 *
 * </p>
 *
 * @ClassName ZtcRouteDao
 * @JDK 1.5
 * @author
 * @date
 */
@Repository("ZtcRouteDao")
public class ZtcRouteDao extends BaseDaoImpl<ZtcRoute> {
	private int pageSize=10;
	public String save(ZtcRoute ztcRoute) {
		this.getHibernateTemplate().save(ztcRoute);
		return ztcRoute.getId();
	}

	public List<ZtcRoute> getAllZtcRoutes() {
		return this.getAll();
	}

	public String delete(String id, String transportId) {
		String sql = "delete from ztc_route where 1=1";
		if (id != null) {
			sql += " and id='" + id + "'";
		}
		if (transportId != null) {
			sql += " and fk_ztc_transport_id='" + transportId + "'";
		}
		if (this.currentSession().createNativeQuery(sql).executeUpdate() >= 0) {
			return "1";
		}
		return "0";
	}

	public List<ZtcRoute> searchInfoEquals(ZtcRoute ztcRoute) {
		String sql = "select * from ztc_route where 1=1";
		if (ztcRoute.getName() != null) {
			sql += " and name='" + ztcRoute.getName() + "'";
		}
		if (ztcRoute.getRemark() != null) {
			sql += " and remark='" + ztcRoute.getRemark() + "'";
		}
		if (ztcRoute.getStatus() != null) {
			sql += " and status='" + ztcRoute.getStatus() + "'";
		}
		if (ztcRoute.getExamineRemark() != null) {
			sql += " and examine_remark='" + ztcRoute.getExamineRemark() + "'";
		}
		if (ztcRoute.getUpdateRemark() != null) {
			sql += " and update_remark='" + ztcRoute.getUpdateRemark() + "'";
		}
		if (ztcRoute.getBeginValidateTime() != null) {
			sql += " and begin_validate_time='" + ztcRoute.getBeginValidateTime() + "'";
		}
		if (ztcRoute.getEndValidateTime() != null) {
			sql += " and end_validate_time='" + ztcRoute.getEndValidateTime() + "'";
		}
		if (ztcRoute.getFkZtcTransportId() != null) {
			sql += " and fk_ztc_transport_id='" + ztcRoute.getFkZtcTransportId() + "'";
		}
		if (ztcRoute.getFkApplyUserId() != null) {
			sql += " and fk_apply_user_id='" + ztcRoute.getFkApplyUserId() + "'";
		}
		if (ztcRoute.getApplyUserName() != null) {
			sql += " and apply_user_name='" + ztcRoute.getApplyUserName() + "'";
		}
		if (ztcRoute.getApplyTime() != null) {
			sql += " and apply_time='" + ztcRoute.getApplyTime() + "'";
		}
		if (ztcRoute.getFkExamineUserId() != null) {
			sql += " and fk_examine_user_id='" + ztcRoute.getFkExamineUserId() + "'";
		}
		if (ztcRoute.getExamineUserName() != null) {
			sql += " and examine_user_name='" + ztcRoute.getExamineUserName() + "'";
		}
		if (ztcRoute.getExamineTime() != null) {
			sql += " and examine_time='" + ztcRoute.getExamineTime() + "'";
		}
		if (ztcRoute.getId() != null) {
			sql += " and id='" + ztcRoute.getId() + "'";
		}
		sql += " order by add_time";
		List<ZtcRoute> list = this.currentSession().createNativeQuery(sql,ZtcRoute.class).getResultList();
		return list;
	}
	public List<ZtcRoute> searchInfoLikes(HashMap<String,Object> arguments) {
		ZtcRoute ztcRoute=(ZtcRoute) arguments.get("ztcRoute");
		String pageNumber=(String)arguments.get("pageNumber");
		String statusSql=(String)arguments.get("statusSql");
		String orderBySql=(String)arguments.get("orderBySql");
		String sql = "select * from ztc_route";
		sql+=statusSql;
		if (ztcRoute.getName() != null) {
			sql += " and ztc_route.name like '%" + ztcRoute.getName() + "%'";
		}
		if (ztcRoute.getRemark() != null) {
			sql += " and ztc_route.remark like '%" + ztcRoute.getRemark() + "%'";
		}
		if (ztcRoute.getExamineRemark() != null) {
			sql += " and ztc_route.examine_remark like '%" + ztcRoute.getExamineRemark() + "%'";
		}
		if (ztcRoute.getUpdateRemark() != null) {
			sql += " and ztc_route.update_remark like '%" + ztcRoute.getUpdateRemark() + "%'";
		}
		if (ztcRoute.getApplyUserName() != null) {
			sql += " and ztc_route.apply_user_name like '%" + ztcRoute.getApplyUserName() + "%'";
		}
		if (ztcRoute.getExamineUserName() != null) {
			sql += " and ztc_route.examine_user_name like '%" + ztcRoute.getExamineUserName() + "%'";
		}
		if (ztcRoute.getFkZtcTransportId() != null) {
			sql += " and ztc_route.fk_ztc_transport_id='" + ztcRoute.getFkZtcTransportId() + "'";
		}
		sql += orderBySql;
		int startIndex=0;
		int endIndex=10;
		if(pageNumber==null){
			pageNumber="0";
		}else{
			int number=Integer.parseInt(pageNumber);
	    	if(!"0".equals(pageNumber)){
	    		number=number-1;
	    	}
	    	startIndex=(number)*pageSize;
		}
		sql+=" limit "+startIndex+","+endIndex;
		List<ZtcRoute> list = this.currentSession().createNativeQuery(sql,ZtcRoute.class).getResultList();
		return list;
	}
	
	public List<ZtcRoute> searchInfoLikesForClassesPublish(HashMap<String,Object> arguments) {
		ZtcRoute ztcRoute=(ZtcRoute) arguments.get("ztcRoute");
		String pageNumber=(String)arguments.get("pageNumber");
		String statusSql=(String)arguments.get("statusSql");
		String orderBySql=(String)arguments.get("orderBySql");
		String sql = "select * from ztc_route";
		sql+=statusSql;
		if (ztcRoute.getName() != null) {
			sql += " and ztc_route.name like '%" + ztcRoute.getName() + "%'";
		}
		if (ztcRoute.getRemark() != null) {
			sql += " and ztc_route.remark like '%" + ztcRoute.getRemark() + "%'";
		}
		if (ztcRoute.getExamineRemark() != null) {
			sql += " and ztc_route.examine_remark like '%" + ztcRoute.getExamineRemark() + "%'";
		}
		if (ztcRoute.getUpdateRemark() != null) {
			sql += " and ztc_route.update_remark like '%" + ztcRoute.getUpdateRemark() + "%'";
		}
		if (ztcRoute.getApplyUserName() != null) {
			sql += " and ztc_route.apply_user_name like '%" + ztcRoute.getApplyUserName() + "%'";
		}
		if (ztcRoute.getExamineUserName() != null) {
			sql += " and ztc_route.examine_user_name like '%" + ztcRoute.getExamineUserName() + "%'";
		}
		if (ztcRoute.getFkZtcTransportId() != null) {
			sql += " and ztc_route.fk_ztc_transport_id='" + ztcRoute.getFkZtcTransportId() + "'";
		}
		sql += orderBySql;
		int startIndex=0;
		int endIndex=10;
		if(pageNumber==null){
			pageNumber="0";
		}else{
			int number=Integer.parseInt(pageNumber);
	    	if(!"0".equals(pageNumber)){
	    		number=number-1;
	    	}
	    	startIndex=(number)*pageSize;
		}
		sql+=" limit "+startIndex+","+endIndex;
		List<ZtcRoute> list = this.currentSession().createNativeQuery(sql,ZtcRoute.class).getResultList();
		return list;
	}


	public String update(ZtcRoute ztcRoute) {
		ZtcRoute ztcRouteBean = this.findUniqueBy("id", ztcRoute.getId());
		if (ztcRoute.getStatus() != null) {
			ztcRouteBean.setStatus(ztcRoute.getStatus());
		}
		if (ztcRoute.getExamineRemark() != null) {
			ztcRouteBean.setExamineRemark(ztcRoute.getExamineRemark());
		}
		if (ztcRoute.getUpdateRemark() != null) {
			ztcRouteBean.setUpdateRemark(ztcRoute.getUpdateRemark());
		}
		if (ztcRoute.getBeginValidateTime() != null) {
			ztcRouteBean.setBeginValidateTime(ztcRoute.getBeginValidateTime());
		}
		if (ztcRoute.getEndValidateTime() != null) {
			ztcRouteBean.setEndValidateTime(ztcRoute.getEndValidateTime());
		}
		if (ztcRoute.getFkExamineUserId() != null) {
			ztcRouteBean.setFkExamineUserId(ztcRoute.getFkExamineUserId());
		}
		if (ztcRoute.getExamineUserName() != null) {
			ztcRouteBean.setExamineUserName(ztcRoute.getExamineUserName());
		}
		if (ztcRoute.getExamineTime() != null) {
			ztcRouteBean.setExamineTime(ztcRoute.getExamineTime());
		}
		if (ztcRoute.getFkUpdateUserId() != null) {
			ztcRouteBean.setFkUpdateUserId(ztcRoute.getFkUpdateUserId());
		}
		if (ztcRoute.getUpdateUserName() != null) {
			ztcRouteBean.setUpdateUserName(ztcRoute.getUpdateUserName());
		}
		if (ztcRoute.getUpdateTime() != null) {
			ztcRouteBean.setUpdateTime(ztcRoute.getUpdateTime());
		}
		this.save(ztcRouteBean);
		if (ztcRouteBean.getId() != null) {
			return "1";
		}
		return "0";
	}
	public int getCount(HashMap<String,Object> arguments) {
		ZtcRoute ztcRoute=(ZtcRoute) arguments.get("ztcRoute");
		String statusSql=(String)arguments.get("statusSql");
		String orderBySql=(String)arguments.get("orderBySql");
		String sql = "select count(*) from ztc_route";
		sql+=statusSql;
		if (ztcRoute.getName() != null) {
			sql += " and ztc_route.name like '%" + ztcRoute.getName() + "%'";
		}
		sql += orderBySql;
		@SuppressWarnings("unchecked")
		List<Object> list= this.getHibernateTemplate().getSessionFactory().getCurrentSession().createNativeQuery(sql).getResultList();
		return new Integer(String.valueOf(list.get(0)));
	}
	public int getCountForClassesPublish(HashMap<String,Object> arguments) {
		ZtcRoute ztcRoute=(ZtcRoute) arguments.get("ztcRoute");
		String statusSql=(String)arguments.get("statusSql");
		String orderBySql=(String)arguments.get("orderBySql");
		String sql = "select count(*) from ztc_route";
		sql+=statusSql;
		if (ztcRoute.getName() != null) {
			sql += " and ztc_route.name like '%" + ztcRoute.getName() + "%'";
		}
		sql += orderBySql;
		@SuppressWarnings("unchecked")
		List<Object> list= this.getHibernateTemplate().getSessionFactory().getCurrentSession().createNativeQuery(sql).getResultList();
		return new Integer(String.valueOf(list.get(0)));
	}

	public List<ZtcRoute> searchOverdueRoute() {
		String sql = "select * from ztc_route where ztc_route.status='1' and ztc_route.end_validate_time<NOW() order by ztc_route.end_validate_time";
		List<ZtcRoute> list = this.currentSession().createNativeQuery(sql,ZtcRoute.class).getResultList();
		return list;
	}

	/**
	 * 根据公司ID获取线路列表信息.
	 * @param tid 公司ID
	 * @return  线路（经纬度）
	 */
	public List<Map> getRouteListMapByTid(String tid, Integer type) {
		String sql = "select  b.id,a.name,concat_ws(';',a.site,b.site) site,concat_ws(';',a.area_city,b.area_city) city,concat_ws(';',a.lat,b.lat) lat from " +
					"(SELECT z.id,z.name, s.name  site, s.area_city, s.lat  " +
					"FROM ztc_route_site s,ztc_route z " +
					"WHERE z.id=s.fk_ztc_route_id AND s.sequence=1 and z.`status`=1 and z.fk_ztc_transport_id='"+tid+"') a " +
					"INNER JOIN (SELECT z.id,z.name, s.name  site, s.area_city, s.lat " +
					"FROM ztc_route_site s,ztc_route z " +
					"WHERE z.id=s.fk_ztc_route_id AND s.sequence=( " +
					"SELECT COUNT(*) " +
					"FROM ztc_route_site s1 " +
					"WHERE s1.fk_ztc_route_id=z.id) and z.`status`=1 and z.fk_ztc_transport_id='"+tid+"') b on a.id=b.id group by a.id; ";
		if(type==3){
			sql="select  b.id,a.name,concat_ws(';',a.site,b.site) site,concat_ws(';',a.area_city,b.area_city) city,concat_ws(';',a.lat,b.lat) lat from  " +
					"(SELECT z.id,z.name, s.name  site, s.area_city, s.lat  " +
					"FROM ztc_route_site s,ztc_route z " +
					"WHERE z.id=s.fk_ztc_route_id AND s.sequence=1 and z.`status`=1 and (z.fk_ztc_transport_id='"+tid+"' or z.fk_ztc_transport_id in (select t.id from ztc_transport t where t.parent_id='"+tid+"'))) a " +
					"INNER JOIN (SELECT z.id,z.name, s.name  site, s.area_city, s.lat " +
					"FROM ztc_route_site s,ztc_route z " +
					"WHERE z.id=s.fk_ztc_route_id  AND s.sequence=( " +
					"SELECT COUNT(*) " +
					"FROM ztc_route_site s1 " +
					"WHERE s1.fk_ztc_route_id=z.id) and z.`status`=1 and (z.fk_ztc_transport_id='"+tid+"' or z.fk_ztc_transport_id in (select t.id from ztc_transport t where t.parent_id='"+tid+"'))) b on a.id=b.id group by a.id ";
		}
		return this.currentSession().createNativeQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
	}
}